﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace XQ.RequestOfficeSupplies.DAL.DAL
{
  public class ChartsDal
    {
        private RequestOfficeSuppliesEntities _ce;

        /// <summary>
        /// 根据月获取部门案件总数
        /// </summary>
        /// <param name="year">年份</param>
        /// <param name="month">月份</param>
        
        /// <returns></returns>
        public int GetCaseNumByDepAndMonth(int year, int month)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                object[] pars =
                {
                    new SqlParameter("@year",year),
                    new SqlParameter("@month",month),
                };
                var sql = new StringBuilder();
                sql.Append("select COUNT(*)Total from Enterprise_HiddenTrouble_Reporting \r\n");
                sql.Append(
                       "where  MONTH(CheckDate)=@month and YEAR(CheckDate)=@year;");
                return _ce.Database.SqlQuery<int>(sql.ToString(), pars).First();
            }
        }


        /// <summary>
        /// 根据月获取部门案件总数
        /// </summary>
        /// <param name="year">年份</param>
        /// <param name="month">月份</param>
        
        /// <returns></returns>
        public int GetCaseNumByDepAndDay(int year, int month, int day)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                object[] pars =
                {
                    new SqlParameter("@year",year),
                    new SqlParameter("@month",month),
                    new SqlParameter("@day" ,day),
        
                };
                var sql = new StringBuilder();
                sql.Append("select COUNT( ID ) from Enterprise_HiddenTrouble_Reporting \r\n");
                //sql.Append("inner join FlowLog fl on c.ID=fl.CaseID \r\n");
                sql.Append(
                    "where  Day(CheckDate)=@day and  MONTH(CheckDate)=@month and YEAR(CheckDate)=@year;");
                return _ce.Database.SqlQuery<int>(sql.ToString(), pars).First();
            }
        }



        /// <summary>
        /// 返回每个部门案件量(月)
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public int GetTotalsByDep(int year, int month, int id)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                object[] pars =
                {
                    new SqlParameter("@depId",id),
                   new SqlParameter("@year",year),
                    new SqlParameter("@month",month),
                };
                var sql = new StringBuilder();
                sql.Append("select COUNT(*) from Enterprise_HiddenTrouble_Reporting er inner join Enterprise_DetailInfo ed \r\n ");
                sql.Append(" on ed.DepartmentId =@depId and ed.ID = er.EnterpriseID and MONTH(er.CheckDate)=@month and YEAR(er.CheckDate)=@year;");
                return _ce.Database.SqlQuery<int>(sql.ToString(), pars).First();
            }

        }


        /// <summary>
        /// 返回每个部门案件量(年)
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public int GetTotalsByDepYear(int year, int id)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                object[] pars =
                {
                    new SqlParameter("@depId",id),
                    new SqlParameter("@year",year),
                    
                };
                var sql = new StringBuilder();
                sql.Append("select COUNT(*) from Enterprise_HiddenTrouble_Reporting er inner join Enterprise_DetailInfo ed \r\n ");
                sql.Append(" on ed.DepartmentId =@depId and ed.ID = er.EnterpriseID and YEAR(er.CheckDate)=@year;");
                return _ce.Database.SqlQuery<int>(sql.ToString(), pars).First();
            }

        }

        /// <summary>
        /// 根据事件类型获取隐患总量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public int GetChartTotalByEventType(int year, int month, string value)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                object[] pars =
                {
                    new SqlParameter("@type",value),
                    new SqlParameter("@year",year),
                    new SqlParameter("@month",month)
                };
                var sql = new StringBuilder();
                sql.Append("select COUNT(*) from Reporting_HiddenTrouble rh inner join HiddenTroubleClassify hc on hc.id = rh.HiddenTroubleID \r\n");
                sql.Append("inner join Enterprise_HiddenTrouble_Reporting ehr on ehr.id=rh.ReportingID and  MONTH(ehr.CheckDate)=@month and YEAR(ehr.CheckDate)=@year and hc.CategoryName = @type");
                return _ce.Database.SqlQuery<int>(sql.ToString(), pars).First();
            }
        }


        /// <summary>
        /// 根据事件类型获取隐患总量(年)
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public int GetTotalsByChartModelsByYear(int year, string value)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                object[] pars =
                {
                    new SqlParameter("@type",value),
                    new SqlParameter("@year",year),
                 
                };
                var sql = new StringBuilder();
                sql.Append("select COUNT(*) from Reporting_HiddenTrouble rh inner join HiddenTroubleClassify hc on hc.id = rh.HiddenTroubleID \r\n");
                sql.Append("inner join Enterprise_HiddenTrouble_Reporting ehr on ehr.id=rh.ReportingID and  YEAR(ehr.CheckDate)=@year and hc.CategoryName = @type");
                return _ce.Database.SqlQuery<int>(sql.ToString(), pars).First();
            }
        }


        /// <summary>
        /// 根据级别隐患总量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public int GetTotalsByLevel(int year, int month, string value)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                object[] pars =
                {
                    new SqlParameter("@type",value),
                    new SqlParameter("@year",year),
                    new SqlParameter("@month",month)
                };
                var sql = new StringBuilder();
                sql.Append("select COUNT(*) from Reporting_HiddenTrouble rh inner join HiddenTroubleClassify hc on hc.id = rh.HiddenTroubleID \r\n");
                sql.Append("inner join Enterprise_HiddenTrouble_Reporting ehr on ehr.id=rh.ReportingID and  MONTH(ehr.CheckDate)=@month and YEAR(ehr.CheckDate)=@year  inner join Sys_Dics sd on  hc.CategoryLevel = sd.ID and sd.Title =@type");
                return _ce.Database.SqlQuery<int>(sql.ToString(), pars).First();
            }
        }


        /// <summary>
        /// 根据级别获取隐患总量(年)
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public int GetTotalsByLevelByYear(int year, string value)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                object[] pars =
                {
                    new SqlParameter("@type",value),
                    new SqlParameter("@year",year),

                };
                var sql = new StringBuilder();
                sql.Append("select COUNT(*) from Reporting_HiddenTrouble rh inner join HiddenTroubleClassify hc on hc.id = rh.HiddenTroubleID \r\n");
                sql.Append("inner join Enterprise_HiddenTrouble_Reporting ehr on ehr.id=rh.ReportingID and  YEAR(ehr.CheckDate)=@year inner join Sys_Dics sd on  hc.CategoryLevel = sd.ID and sd.Title =@type");
                return _ce.Database.SqlQuery<int>(sql.ToString(), pars).First();
            }
        }
    }
}
